﻿using System;
using System.Data;
using System.Data.OleDb;

namespace WinformMDB
{
    class MDBConn
    {
        private string _fileName;
        private string _connectionString;
        private OleDbConnection _odcConnection;
        private OleDbCommand cmd = null;

        /// <summary>
        /// 构建函数
        /// </summary>
        /// <param name="fileName">MDB文件</param>
        public MDBConn(string fileName)
        {
            this._fileName = fileName;
            this._connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";";
        }
        
        /// <summary>
        /// 建立连接（打开数据库文件）
        /// </summary>
        public void Open()
        {
            try
            {
                // 建立连接
                this._odcConnection = new OleDbConnection(this._connectionString);
                // 打开连接
                this._odcConnection.Open();
            }
            catch (Exception)
            {
                throw new Exception("try open " + this._fileName + " failed, Please check the path if correct. ");
            }
        }

        /// <summary>
        /// 断开连接（关闭据库文件）
        /// </summary>
        public void Close()
        {
            this._odcConnection.Close();
        }

        /// <summary>
        /// 将sql语句和dbConnect用Command连起来
        /// </summary>
        /// <param name="sql">sql命令</param>
        /// <returns>返回绑定的Command</returns>
        public OleDbCommand WriteInSQL(string sqlComm)
        {
            OleDbCommand cmd = new OleDbCommand(sqlComm, this._odcConnection);
            //cmd = this._odcConnection.CreateCommand();
            //cmd.CommandText = sqlComm;
            return cmd;
        }

        /// <summary>
        /// 根据sql命令返回一个DataSet
        /// </summary>
        /// <param name="sql">sql命令</param>
        /// <returns>以DataTable形式返回数据</returns>
        public DataTable GetDataSetByAdapter(string sql)
        {
            DataTable dt = new DataTable();
            DataSet ds = new DataSet();
            try
            {
                OleDbDataAdapter adapter = new OleDbDataAdapter(sql, this._odcConnection);
                adapter.Fill(dt);
            }
            catch (Exception)
            {
                throw new Exception("sql command ： " + sql + " run failed！");
            }
            return dt;
        }

        /// <summary>
        /// 从OleDbCommand中读取DataSet
        /// </summary>
        /// <param name="Command">OleDbCommand参数</param>
        /// <returns>以DataTable形式返回数据</returns>
        public DataTable GetDataSetByReader(OleDbCommand odCommand)
        {
            //Reader需要和WriteInSQL同时使用
            DataTable dt = new DataTable();
            DataRow dr;
            try
            {
                OleDbDataReader odrReader = odCommand.ExecuteReader(); //Reader在这里  
                int size = odrReader.FieldCount;
                for (int i = 0; i < size; i++)
                {
                    DataColumn dc;
                    dc = new DataColumn(odrReader.GetName(i));
                    dt.Columns.Add(dc);
                }
                while (odrReader.Read())
                {
                    dr = dt.NewRow();
                    for (int i = 0; i < size; i++)
                    {
                        if(odrReader.GetName(i) == "FZBZ")
                        {
                            if ((bool)odrReader[odrReader.GetName(i)] == true)
                                dr[odrReader.GetName(i)] = 0;
                            else
                                dr[odrReader.GetName(i)] = -1;
                        }
                        else
                            dr[odrReader.GetName(i)] = odrReader[odrReader.GetName(i)].ToString().Trim();
                    }
                    dt.Rows.Add(dr);
                }
                odrReader.Close();
                odrReader = null;
            }
            catch (Exception)
            {
                throw new Exception("sql command ：Reader run failed！");
            }
            return dt;
        }

        public Object GetValue(OleDbCommand command, int row, string col)
        {
            OleDbDataReader Reader = command.ExecuteReader(); //Reader在这里  
            object value = null;
            int i = 0;
            while (Reader.Read())
            {
                if (i == row)
                {
                    value = Reader[col];
                    break;
                }
                i++;
            }
            Reader.Close();
            return value;
        }
    
}
}
